﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DAL;
using DTO;
using System.Collections;
using System.Globalization;
namespace BLL
{
    public class CanBoBLL
    {
        static int id = 0;
        QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
        public IQueryable SelectDes()
        {
        //    return DB.CanBos.OrderByDescending(s => s.MaCanBo).AsQueryable();
            return DB.CanBos.Join(
                   DB.ChucVus,
                   a => a.MaChucVu,
                   b => b.MaChucVu,
                   (a, b) => new
                   {
                       a.MaCanBo,
                       a.HoTen,
                       a.GioiTinh,
                       a.NgaySinh,
                       a.DanToc,
                       a.TonGiao,
                       a.CMND,
                       a.DiaChi,
                       a.Email,
                       b.TenChucVu,
                       a.SoDienThoai,
                       a.IsGiaoVien
                   }
                   ).AsQueryable();
        }
        public string Insert(CanBoDTO canbo,string mato)
        {
            try
            {
                CanBo cb = new CanBo();
                cb.MaCanBo = (id++).ToString();
                cb.HoTen = canbo.Hoten;
                cb.GioiTinh = canbo.Gioitinh;
                cb.NgaySinh = canbo.Ngaysinh;
                cb.DanToc = canbo.Dantoc;
                cb.TonGiao = canbo.Tongiao;
                cb.CMND = canbo.Cmnd;
                cb.DiaChi = canbo.Diachi;
                cb.Email = canbo.Email;
                //String a = DB.ChucVus.Where(p => p.MaChucVu == canbo.Machucvu).FirstOrDefault().TenChucVu;
                cb.MaChucVu = canbo.Machucvu;
                cb.SoDienThoai = canbo.Sodienthoai;
                cb.IsGiaoVien = canbo.Isgiaovien;
                DB.CanBos.InsertOnSubmit(cb);
                DB.SubmitChanges();
                //insert vao bang giao vien
                GiaoVien gv = new GiaoVien();
                CanBo cb2 = DB.CanBos.OrderByDescending(s=>s.MaCanBo).FirstOrDefault();
                gv.MaCanBo = cb2.MaCanBo;
                gv.MaTo = mato;
                DB.GiaoViens.InsertOnSubmit(gv);
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public string Update(CanBoDTO canbo,string mato)
        {
            try
            {
                CanBo cb = DB.CanBos.Where(s => s.MaCanBo == canbo.Macanbo).FirstOrDefault();
                cb.HoTen = canbo.Hoten;
                cb.GioiTinh = canbo.Gioitinh;
                cb.NgaySinh = canbo.Ngaysinh;
                cb.DanToc = canbo.Dantoc;
                cb.TonGiao = canbo.Tongiao;
                cb.CMND = canbo.Cmnd;
                cb.DiaChi = canbo.Diachi;
                cb.Email = canbo.Email;
                cb.MaChucVu = canbo.Machucvu;
                cb.SoDienThoai = canbo.Sodienthoai;
                cb.IsGiaoVien = canbo.Isgiaovien;
                if (canbo.Isgiaovien)
                {
                    //delete cai co san
                    GiaoVien gv = DB.GiaoViens.Where(s => s.MaCanBo == canbo.Macanbo).FirstOrDefault();
                    DB.GiaoViens.DeleteOnSubmit(gv);
                    DB.SubmitChanges();
                    //them moi vi bang giao vien 2 truong nay la khoa nen ta ko the update
                    GiaoVien gv2 = new GiaoVien();
                    gv2.MaCanBo = canbo.Macanbo;
                    gv2.MaTo = mato;
                    DB.GiaoViens.InsertOnSubmit(gv2);
                }
                else
                {
                    GiaoVien gv = DB.GiaoViens.Where(s => s.MaCanBo == canbo.Macanbo).FirstOrDefault();
                    DB.GiaoViens.DeleteOnSubmit(gv);
                }
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        public string[] Getchucvu()
        {

            string[] data = new string[1000];
            data = (from t in DB.ChucVus
                    select t.TenChucVu).ToArray();
            return data;
        }
        private string loi = "Khong Loi";
        // Ham in ra loi
        public string inloi()
        {
            return loi;
        }
        public DateTime chuyenkieudatetime(string time)
        {
            DateTime datetime = Convert.ToDateTime("1/1/1990");
            try
            {
                DateTimeFormatInfo dtfi = new DateTimeFormatInfo();
                dtfi.ShortDatePattern = "MM/dd/yyyy";
                dtfi.DateSeparator = "/";
                datetime = Convert.ToDateTime(time, dtfi);
            }
            catch (FormatException e)
            {
                loi = e.Message;
            }
            return datetime;
        }

        public string Delete()
        {
            try
            {
                DB.SubmitChanges();
                return "Thanh cong";

            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
         public string laymacv(string tenchucvu)
        {
            QuanLyDiemDataContext DB = new QuanLyDiemDataContext();
           // return DB.CanBos.Where(s => s.MaCanBo == macanbo).Select(x => x.HoTen).FirstOrDefault();
            return DB.ChucVus.Where(p => p.TenChucVu == tenchucvu).Select (x =>x.MaChucVu).FirstOrDefault();//.TenChucVu;
           // a = txtChucVu.Text;
         }
         public IQueryable TimKiemTen(string hoten)
         {
             return DB.CanBos.Where(s => s.HoTen.Contains(hoten)).OrderByDescending(s => s.MaCanBo).AsQueryable();
         }
         public string getTenTo(string macanbo)
         {
             return DB.GiaoViens.Join(
                 DB.Tos,
                 a => a.MaTo,
                 b => b.MaTo,
                 (a, b) => new
                 {
                     a.MaCanBo,
                     b.TenTo
                 }
                 ).Where(s=>s.MaCanBo==macanbo).Select(s => s.TenTo).FirstOrDefault().ToString();
         }

    }

}
